create type
Define a new user-defined data type
Syntax
create_type:
CREATE TYPE type_name AS {data_type | (member_name data_type [, member_name data_type]...)}
Description
The CREATE TYPE statement is an SQL standard statement that allows the user to define their own data types. The user-defined type may consist of a single data type (referred to as a "simple user-defined type") or a set of member columns (referred to as a "structured user-defined type").
Simple User-Defined Type
Syntax
CREATE TYPE type_name AS data_type
Components
The CREATE TYPE statement includes the following components for a simple user-defined type:
| type_name | Name of the user-defined type |
| data_type | Data type of the user-defined type |
Comments
The simple user-defined type may be any supported RaimaDB data type except for ENUM types and BLOB/CLOB types (e.g.LONG VARBINARY and LONG VARCHAR). The simple user-defined type may be included in any column constraint or DEFAULT value attributes. For DEFAULT values, the values assigned to the user-defined type must be valid for the original RaimaDB data type.
Examples
The following schema examples show some of the uses for the simple user-defined type:
CREATE TYPE text AS CHAR (400); CREATE TYPE money AS DECIMAL(10,2);
In the generated C/C++ header file from the rdm-compile schema compiler tool, the simple user-defined type is represented as a C-style typedef where the type name is typedef'd as the specified data type. For instance, the above user-defined types of TEXT and MONEY appear in the header file as follows:
/** \brief type declaration for user-defined type TEXT */ typedef char TEXT[1601]; /** \brief type declaration for user-defined type MONEY */ typedef RDM_BCD_T MONEY;
Note that CHAR (400) means a string that holds up to 400 characters. Since RaimaDB uses UTF-8 encoding, one character can take up to four bytes so, 400 characters requires up to 1601 bytes including the null terminator. The OCTETS keyword can be used to specify the number of bytes instead of the number of characters. (See Character Types).
Structured User-Defined Type
Syntax
CREATE TYPE type_name AS (member_name data_type [, member_name data_type]...)
Components
The CREATE TYPE statement includes the following components for a structured user-defined type:
| type_name | Name of the user-defined type |
| member_name | Name of the member of the structured user-defined type |
| data_type | Data type of the user-defined type |
Comments
The structured user-defined type cannot be indexed.
The data type for each member of the structured user-defined type can be any data type RaimaDB supports as well as any user-defined type and ENUMs, except for the BLOB/CLOB types. A structured user-defined type can include another structured user-defined type. The current limit to nesting user-defined types is 3 levels. The structured user-defined type does not support the DEFAULT values attribute and only provides limited support for column constraints. The structured user-defined type does not support the being included in a KEY (index) or FOREIGN KEY (references) constraint.
Examples
-- create a structured user-defined type
CREATE TYPE manufacturer_data AS
(
manufacturer_code UINT32,
manufacturer_name CHAR (20),
manufacturer_state CHAR (2 OCTETS)
);
-- created a nested structured user-defined type
CREATE TYPE product AS
(
product_id UINT64,
product_name CHAR (20),
manufacturer MANUFACTURER_DATA
);
-- Use the TEXT and PRODUCT user-defined types in a table
CREATE TABLE test_table
(
test_id ROWID PRIMARY KEY,
test_name CHAR (20),
test_description TEXT,
test_product PRODUCT
);
The structured user-defined type is represented as a C-style structure where the type name is the typedef'd name of the structure in the generated header file. For example, the user-defined types, MANUFACTURER_DATA and PRODUCT, are represented as the following structures:
/** \brief type declaration for user-defined type MANUFACTURER_DATA */
typedef struct MANUFACTURER_DATA_S
{
uint32_t MANUFACTURER_CODE;
char MANUFACTURER_NAME[81];
char MANUFACTURER_STATE[3];
} MANUFACTURER_DATA;
/** \brief type declaration for user-defined type PRODUCT */
typedef struct PRODUCT_S
{
uint64_t PRODUCT_ID;
char PRODUCT_NAME[81];
MANUFACTURER_DATA MANUFACTURER;
} PRODUCT;
Note that the size of the MANUFACTURER_STATE member column is 3, corresponding to CHAR (2 OCTETS) in the CREATE TYPE statement.